package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.dao.base.RegionForBgDao;
import com.hnkywl.sts.entity.jhgl.Bgjh;
import net.ruixin.orm.hibernate.OrderBy;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: ZHUCHG
 * Date: 13-6-26
 * Time: 下午3:20
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BgjhDao extends SimpleHibernateTemplate<Bgjh, Long> {

    public List<Bgjh> findBgjhByIds(List<Long> ids) {
        return createCriteria(new Criterion[]{Restrictions.in("id", ids)}).list();
    }

    //根据计划汇总ID查询计划的集合
    public List<Bgjh> findBgjhListByBgjhhzId(Long id) {
        OrderBy[] orderBy = new OrderBy[]{new OrderBy("id", OrderBy.OrderType.ASC)};
//        OrderBy order = new OrderBy("id", OrderBy.OrderType.ASC);
//        orderBy[0] = order;
        return this.findByProperty("bgjhhz.id", id, orderBy);
    }

    public Page<Map> findList(String type, Long jhdwId, Long gyzId, Integer jhnf, Integer jhyf, Date startdata, Date enddate, String code, String sm, Integer zt, String sbcl, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select d.hz as \"hz\",d.id as \"id\",d.lj_id as \"lj_id\",d.jyjg_id as \"jyjgId\",d.qc as \"jyjg\",d.jhdw as \"jhdw\",d.jhdw_id as \"jhdwId\",d.sm as \"sm\",d.date_created as \"jhny\"," +
                "d.gyz as \"gyz\",d.xm as \"zdr\",d.zt as \"zt\",d.type as \"type\",d.bz as \"bz\" ";
        String subCondition = StringUtils.EMPTY;
        String subHzCondition = StringUtils.EMPTY;
        if (StringUtils.isNotEmpty(code)) {
            subCondition += " and b.id in (select mx.bgjh_id from t_biz_bgjhmx mx,t_sys_gg gg where mx.wzgg_id=gg.id and gg.deleted=0 and mx.deleted=0 and (gg.oldcode like :oldcode))";
            subHzCondition += " and h.id in (select mx.bgjhhz_id from t_biz_bgjhhzmx mx,t_sys_gg gg where mx.wzgg_id=gg.id and gg.deleted=0 and mx.deleted=0 and (gg.oldcode like :hzoldcode))";
            param.put("oldcode", code + "%");
            param.put("hzoldcode", code + "%");
        }
        if (StringUtils.isNotEmpty(sbcl)) {
            subCondition += " and b.id in (select mx.bgjh_id from t_biz_bgjhmx mx where  mx.deleted=0 and mx.sbcl=:sbcl)";
            subHzCondition += " and h.id in (select mx.bgjhhz_id from t_biz_bgjhhzmx mx where  mx.deleted=0 and mx.sbcl=:hzsbcl)";
            param.put("sbcl", sbcl);
            param.put("hzsbcl", sbcl);
        }
        String sqlBase = "select 0 as hz,b.id,b.lj_id,b.jyjg_id,jyjg.qc,b.jhdw_id,(select j.qc from t_sys_gsjg j where b.jhdw_id=j.id and j.deleted=0) jhdw,b.gyz_id," +
                " (select g.qc from t_sys_gsjg g where b.gyz_id=g.id and g.deleted=0) gyz,b.sm,b.date_created,b.createuser_id,b.createorgan_id,b.createdepartment_id," +
                " y.xm,y.zggs_id,y.zgjg_id,b.zt,b.deleted,b.type,b.jhnf,b.jhyf,b.bz " +
                "  from t_biz_bgjh b left join t_sys_gsjg jyjg on b.jyjg_id = jyjg.id" +
                "  left join t_sys_yg y on b.createuser_id=y.id and y.deleted=0 " +
                "  where  b.deleted=0  " + subCondition +
                " union all " +
                " select  1 as hz,h.id, h.lj_id,h.jyjg_id,jyjg.qc,h.jhdw_id,(select j.qc from t_sys_gsjg j where h.jhdw_id=j.id and j.deleted=0) jhdw," +
                " h.gyz_id,(select g.qc from t_sys_gsjg g where h.gyz_id=g.id and g.deleted=0) gyz,h.sm,h.date_created,h.createuser_id,h.createorgan_id,h.createdepartment_id, " +
                " y.xm,y.zggs_id,y.zgjg_id,h.zt,h.deleted,h.type,h.jhnf,h.jhyf,'' " +
                " from t_biz_bgjhhz h left join t_sys_gsjg jyjg on h.jyjg_id = jyjg.id" +
                " left join t_sys_yg y on h.createuser_id=y.id and y.deleted=0 " +
                " where  h.deleted=0 and h.type='ydjh' " + subHzCondition;
        if (StringUtils.isNotEmpty(type)) {
            condition.add(" d.type=:type ");
            param.put("type", type);
        }
        if (startdata != null) {
            condition.add(" d.date_created>= :startdata ");
            param.put("startdata", startdata);
        }
        if (enddate != null) {
            condition.add(" d.date_created< :enddate ");
            param.put("enddate", enddate);
        }

        if (jhdwId != null) {
            condition.add(" d.jhdw_id=:jhdwId ");
            param.put("jhdwId", jhdwId);
        }
        if (gyzId != null) {
            condition.add(" d.gyz_id=:gyzId ");
            param.put("gyzId", gyzId);
        }
        if (jhnf != null) {
            condition.add(" d.jhnf=:jhnf ");
            param.put("jhnf", jhnf);
        }
        if (jhyf != null) {
            condition.add(" d.jhyf=:jhyf ");
            param.put("jhyf", jhyf);
        }
        if (StringUtils.isNotEmpty(sm)) {
            condition.add(" d.sm like :sm ");
            param.put("sm", "%" + sm + "%");
        }

        if (null != zt) {
            condition.add(" d.zt = :zt ");
            param.put("zt", zt);
        }

        String dataRegion = RegionForBgDao.createSQLDataRegion(null, "d", false);
        if (StringUtils.isNotEmpty(dataRegion))
            condition.add(dataRegion);
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by d.date_created desc ";
        String countSql = "select count(1) from (" + sqlBase + ") d " + whereSql;
        String resultSql = sqlColumn + " from (" + sqlBase + ") d" + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 首页显示保供计划数
     *
     * @param year
     * @param month
     * @return
     */
    public List findBgjhList(Integer year, Integer month) {
        String dataRegion = RegionForBgDao.createSQLDataRegion(null, "y", false);
        String whereSql = " where t.deleted ='0' and t.zt= 1 and t.jhnf = ? and t.jhyf = ? ";
        String sql = " select t.type from t_biz_bgjh t left join t_sys_yg y on t.createuser_id = y.id and y.deleted=0 " +
                whereSql + (StringUtils.isEmpty(dataRegion) ? "" : "and " + dataRegion);
        return this.findBySQL(sql, year, month);
    }

    /**
     * 根据计划ID查询退回计划明细
     *
     * @param id
     * @return
     */
    public List<Map> findThBgjhmxByBgjh(Long id) {
        String sql = "select mx.wzgg_id \"wzgg.id\",g.code \"wzgg.code\",g.oldcode \"wzgg.oldcode\",g.name \"wzgg.name\", " +
                "g.ggxh \"wzgg.ggxh\",g.dw \"wzgg.dw\",mx.ph \"ph\",mx.jhsl \"jhsl\",mx.dhrq \"dhrq\",mx.bz \"bz\"," +
                "mx.sbcl \"sbcl\",hzmx.thyy \"bgjhhzmx.thyy\",hzmx.thrq \"bgjhhzmx.thrq\",hzmx.thr \"bgjhhzmx.thr\" " +
                " from t_biz_bgjh jh,t_biz_bgjhhz hz,t_biz_bgjhmx mx,t_biz_bgjhhzmx hzmx,t_sys_gg g " +
                " where jh.bgjhhz_id=hz.id and jh.id=mx.bgjh_id and hz.id=hzmx.bgjhhz_id and mx.wzgg_id=hzmx.wzgg_id " +
                " and g.id=mx.wzgg_id and jh.deleted=0 and mx.deleted=0 and hzmx.zt=10 and jh.id=?";
        return createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }


    public Boolean checkWzmx(Long id, String wzbm) {
        String sql = "select count(1) from t_biz_bgjh jh,t_biz_bgjhmx mx,t_sys_gg gg where jh.id=mx.bgjh_id and jh.id=? and gg.id=mx.wzgg_id and gg.code = ?";
        Object object = createSQLQuery(sql, id, wzbm).uniqueResult();
        return (object == null || object.equals(0));
    }

    public Page<Bgjh> findBgjhListsByBgjhhzId(Long id, int pageNo, int pageSize) {
        Page<Bgjh> page = new Page<Bgjh>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(1000);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("jyjg", "jyjg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("bgjhhz", "bgjhhz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("this_.bm as bm",new String[]{"bm"},new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("this_.sm as sm", new String[]{"sm"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("dateCreated").as("jhsj"))//计划时间
                        .add(Property.forName("jyjg.id").as("jyjg.id"))
                        .add(Property.forName("jyjg.qc").as("jyjg.qc")) //物流园
                        .add(Property.forName("createUser.id").as("createUser.id"))
                        .add(Property.forName("createUser.xm").as("createUser.xm")) //创建人
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (id != null)
            criteria.add(Property.forName("bgjhhz.id").eq(id));
        return this.findByCriteria(page, criteria);
    }
}
